﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace FinanSoft.DataLayer
{

    /// <summary>
    /// Es la implementación de la interfaz para acceso directo a datos
    /// ESPECIFICAMENTE para SQL Server
    /// </summary>
    public class SqlDataHelper : IDataHelper<SqlDataReader, SqlParameter[], SqlParameter>, IDisposable
    {
        /// <summary>
        /// Nombre estándar para el "parámetro" de retorno de un stored procedure
        /// </summary>
        private const string OUTPUT_PARAMETER_NAME = "@Return_Value";

        /// <summary>
        /// Creo el SQLDataHelper con una conexión a una base de datos de SQL Server
        /// </summary>
        /// <param name="connectionString">String de conexión a la base de datos</param>
        public SqlDataHelper(string connectionString)
        {
            _connection = new SqlConnection(connectionString);
            _reader = null;
        }

        /// <summary>
        /// Conexión con la base de datos de SQL Server
        /// </summary>
        protected SqlConnection _connection;
        /// <summary>
        /// DataReader de SQL que contiene resultados de una consulta o de stored procedure de consulta
        /// </summary>
        protected SqlDataReader _reader;

        #region IDataManager Members

        /// <summary>
        /// Opens the connection if it is not already opened.
        /// </summary>
        /// <returns></returns>
        public bool OpenConnection()
        {
            if (_connection.State != ConnectionState.Open)
                _connection.Open();
            return true;
        }

        /// <summary>
        /// Closes the connection if it is not already closed.
        /// </summary>
        public void CloseConnection()
        {
            CloseReader();
            if (_connection.State != ConnectionState.Closed)
                _connection.Close();
        }

        /// <summary>
        /// Executes an stored procedure that returns rows, when the CloseReader method is called the connection to the database will be closed.
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <returns></returns>
        public SqlDataReader ExecuteStoredProcedure(string spName, SqlParameter[] parameters)
        {
            int spResult;
            return ExecuteStoredProcedure(spName, parameters, out spResult);
        }

        /// <summary>
        /// Executes an stored procedure that returns rows, when the CloseReader method is called the connection to the database will be closed.
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <param name="spResult">Stores the stored procedure result</param>
        /// <returns></returns>
        public SqlDataReader ExecuteStoredProcedure(string spName, SqlParameter[] parameters, out int spResult)
        {
            return ExecuteStoredProcedure(spName, parameters, true, out spResult);
        }

        /// <summary>
        /// Executes an stored procedure that returns rows
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed when the CloseReader method is invoked</param>
        /// <returns></returns>
        public SqlDataReader ExecuteStoredProcedure(string spName, SqlParameter[] parameters, bool closeConnection)
        {
            int spResult;
            return ExecuteStoredProcedure(spName, parameters, true, out spResult);
        }

        /// <summary>
        /// Executes an stored procedure that returns rows
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed when the CloseReader method is invoked</param>
        /// <param name="spResult">Stores the stored procedure result</param>
        /// <returns></returns>
        public SqlDataReader ExecuteStoredProcedure(string spName, SqlParameter[] parameters, bool closeConnection, out int spResult)
        {
            SqlCommand command = createCommand(spName, parameters, true);
            command.CommandType = CommandType.StoredProcedure;
            setReader(command, closeConnection);
            spResult = Convert.ToInt32(command.Parameters[0].Value);
            return _reader;
        }

        /// <summary>
        /// Executes an stored procedure that does not return rows and closes the connection to the database
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        public void ExecuteNoResultStoredProcedure(string spName, SqlParameter[] parameters)
        {
            int spResult;
            ExecuteNoResultStoredProcedure(spName, parameters, out spResult);
        }

        public void ExecuteNoResultStoredProcedure(string spName, SqlParameter[] parameters, out int spResult)
        {
            ExecuteNoResultStoredProcedure(spName, parameters, true, out spResult);
        }

        public void ExecuteNoResultStoredProcedure(string spName, SqlParameter[] parameters, bool closeConnection)
        {
            int spResult;
            ExecuteNoResultStoredProcedure(spName, parameters, closeConnection, out spResult);
        }

        /// <summary>
        /// Executes an stored procedure that does not return rows
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed</param>
        public void ExecuteNoResultStoredProcedure(string spName, SqlParameter[] parameters, bool closeConnection, out int spResult)
        {
            SqlCommand command = createCommand(spName, parameters, true);
            command.CommandType = CommandType.StoredProcedure;
            executeNonResult(command, closeConnection);
            spResult = Convert.ToInt32(command.Parameters[0].Value);
        }

        /// <summary>
        /// Executes a query that returns rows, when the CloseReader method is called the connection to the database will be closed.
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the query</param>
        /// <returns></returns>
        public SqlDataReader ExecuteQuery(string query, SqlParameter[] parameters)
        {
            return ExecuteQuery(query, parameters, true);
        }

        /// <summary>
        /// Executes a query that returns rows
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the query</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed when the CloseReader method is invoked</param>
        public SqlDataReader ExecuteQuery(string query, SqlParameter[] parameters, bool closeConnection)
        {
            SqlCommand command = createCommand(query, parameters);
            setReader(command, closeConnection);
            return _reader;
        }

        /// <summary>
        /// Executes an query that does not return rows and closes the connection to the database
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the query</param>
        public void ExecuteNoResultQuery(string query, SqlParameter[] parameters)
        {
            ExecuteNoResultQuery(query, parameters, true);
        }

        /// <summary>
        /// Executes an query that does not return rows
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the query</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed</param>
        public void ExecuteNoResultQuery(string query, SqlParameter[] parameters, bool closeConnection)
        {
            SqlCommand command = createCommand(query, parameters);
            executeNonResult(command, closeConnection);
        }

        public void CloseReader()
        {
            if (_reader != null && !_reader.IsClosed)
                _reader.Close();
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            CloseConnection();
        }

        #endregion

        private void setReader(SqlCommand command, bool closeConnection)
        {
            OpenConnection();
            if (closeConnection)
                _reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            else
                _reader = command.ExecuteReader();
        }

        private void executeNonResult(SqlCommand command, bool closeConnection)
        {
            OpenConnection();
            command.ExecuteNonQuery();
            if (closeConnection)
                CloseConnection();
        }

        private SqlCommand createCommand(string text, SqlParameter[] parameters)
        {
            return createCommand(text, parameters, false);
        }

        private SqlCommand createCommand(string text, SqlParameter[] parameters, bool addOutputParameter)
        {
            SqlCommand result = new SqlCommand(text, _connection);
            if (addOutputParameter)
            {
                var outParam = new SqlParameter(OUTPUT_PARAMETER_NAME, SqlDbType.Int);
                outParam.Direction = ParameterDirection.ReturnValue;
                result.Parameters.Add(outParam);
            }
            foreach (SqlParameter sqlParameter in parameters)
                if (sqlParameter.Direction != ParameterDirection.ReturnValue || !addOutputParameter)
                    result.Parameters.Add(sqlParameter);
            return result;
        }
    }
}